/**
 * title: FeatureSelectionDAO.java
 * description: Program that provides abstract interface to the database with the
 *              help of the FeatureSelection model, which encapsulates feature selection related items.
 * date: December 28 2013
 * id: 2540434
 * course: COMP 495 (Project-Server)
 * @author Mohamed B Thaha
 * @version 1.0
 * @copyright 2013 Mohamed B Thaha
 */

/**
 * DOCUMENTATION...
 */

/**                                                                               
 *
 *<H1>FeatureSelectionDAO</H1>
 *
 *<H3>Purpose and Description</H3>
 *
 *<P>
 * An application that allows the job of the DAO (data access object) to provide 
 * an abstract interface to the database.
 *</P>
 *<P>
 * The DAOs main purpose is to perform CRUD operations on the database, which basically 
 * means create, retrieve, update, and delete data by virtue of a database connection or 
 * JDBC API driver.
 *</P>
 *<P>
 * This program requires the Sun Java SDK version 1.7 or better.
 *</P>
 *                                                                              
 *<DL>
 *<DT> Compiling and running instructions</DT>
 *<DT> Assuming SDK 1.7 (or later) and the CLASSPATH are set up properly.</DT>
 *<DT> Assuming the use of Eclipse IDE (Kepler) 4.3.1.</DT>
 *<DT> Change to the directory containing the source code.</DT>
 *<DD> Compile:    FeatureSelectionDAO.java</DD>
 *<DD> Run:        FeatureSelectionDAO.java</DD>
 *<DD> Document:   Generate Javadoc FeatureSelectionDAO</DD>
 *</DL>
 */

/**
 *
 * <H3>Classes</H3>
 *
 *<P>
 * public class FeatureSelectionDAO {<BR>
 * This is the main public class for this application. It contains all the
 * methods required to perform CRUD operations.
 *</P>
 *
 * <H3>FeatureSelectionDAO Methods</H3>
 *
 *<P>
   public static boolean create(FeatureSelection tracking, long deviceID) throws Exception {<BR>
   This method returns either a true value indicating that the device usage has been
   registered into the database or a false value indicating that the insertion
   has failed.
 *</P>
 * 
 *<P>
   public static FeatureSelection[] select(Hashtable<String, Object> featureSelectionFilters) throws Exception {<BR>
   This method returns an array of FeatureSelections if such exists. The method also coincides
   with the TrackedFeature select method which retrieves an array of selected features as it relates
   to that particular device.
 *</P>
 *
 *<P>
   public static boolean update(long deviceID, long userID, Hashtable<String, Object> args) throws Exception {<BR>
   This method returns either a true value indicating that the update was a success 
   or a false value indicating that the update has failed.
 *</P> 
 *
 *<P>
   public static boolean delete(long deviceID, long userID) throws Exception {<BR>
   This method returns either a true value indicating a single device usage has 
   been deleted or a false value indicating that the operation has failed.
 *</P> 
 * 
 * <H3>FeatureSelectionDAO Instance Variables (Tags - Constants)</H3>
 *
 *<P>
 * public static final String DEVICE_ID = "device_id"
 * public static final String USER_ID = "user_id"
 * public static final String START_TIME = "start_time"
 * public static final String END_TIME = "end_time"
 * public static final String USAGE = "usage"
 * public static final String PASSWORD = "password"
 *</P>
 */

/**
 * CODE...
 */

package athabasca.ca.dao;
/** Java core packages */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;

import athabasca.ca.model.FeatureSelection;
import athabasca.ca.model.TrackedFeature;
import athabasca.ca.model.User;
import athabasca.ca.util.*;
/** primary (public) class for FeatureSelectionDAO */
public class FeatureSelectionDAO {
	public static final String DEVICE_ID = "device_id";
	public static final String USER_ID = "user_id";
	public static final String START_TIME = "start_time";
	public static final String END_TIME = "end_time";
	public static final String USAGE = "usage";
	public static final String PASSWORD = "password";
	
	/** 
	 * Method's purpose is to insert a new device usage into the database
	 * @param userID
	 * user identification number
	 * @param deviceID
	 * device identification number
	 * @param start
	 * device start time
	 * @param end
	 * device end time
	 * @return
	 * boolean
	 * @throws Exception
	 */
	public static boolean create(FeatureSelection tracking, long deviceID) throws Exception {
		Connection conn = null;
		PreparedStatement query = null;
		boolean isTrue = false;
		
		TrackedFeature[] tF = tracking.getTrackedFeatures();
		
		try {
			// A 'safeguard' that makes sure the FeatureSelection object is not empty 
			// otherwise do not proceed to execute the rest of the code.
			if ((tracking.getStartDate() == null) || (tracking.getTrackedUser().getUserId() == 0) || (deviceID <= 0))
				return false;
			else {
			
			conn = postgreSQL.postgreSQLConn();
			
			boolean insertTF = false;

			// This query will only insert data into the device usage table.
			query = conn.prepareStatement("INSERT INTO device_usage " + 
					"VALUES (?, ?, ?, ?)");
			query.setObject(1, deviceID);
			query.setObject(2, tracking.getStartDate());
			query.setObject(3, null);
			query.setObject(4, tracking.getTrackedUser().getUserId());
			int insertUsage = query.executeUpdate();
			query.close();
			
			if (insertUsage == 1) {
				for (int i = 0; i < tF.length; i++) {
					insertTF = TrackedFeatureDAO.create(tF[i], deviceID, tracking.getTrackedUser().getUserId());
				}
			}
			
			// We must make sure that data was inserted in the device usage
    		// table.
			if ((insertUsage == 1) && (insertTF == true)) {
				isTrue = true;
			}
			}
				
		} catch (ArrayIndexOutOfBoundsException e) {
			e.getMessage();
		} catch (NullPointerException e) {
			e.getMessage();
		} catch (SQLException sqlError) {
			sqlError.getMessage();
		} catch (Exception e) {
			e.getMessage();
			e.printStackTrace();
		} finally {
			if (conn != null) conn.close();
		}
		
		return isTrue;
	}
	
	/** 
	 * Method's purpose is to retrieve an array of FeatureSelections along with selected TrackedFeature array
	 * @param featureSelectionFilters
	 * hash table
	 * @return
	 * FeatureSelection[]
	 * @throws Exception
	 */
	public static FeatureSelection[] select(Hashtable<String, Object> featureSelectionFilters) throws Exception {
		PreparedStatement query = null;
		Connection conn = null;
		
		ArrayList<FeatureSelection> getSelection = new ArrayList<FeatureSelection>();
		
		try {
			// If the filters contained in the hash table are empty, return a null value 
			// and do not proceed to execute the rest of the code.
			if (featureSelectionFilters.isEmpty())
				return null;
			else {
			
			String sql = "";
			String key = "";
			String statement = "";
			String statement2 = "";
			
			Enumeration<String> keys = featureSelectionFilters.keys( );
			
			Hashtable<String, Object> filters;
			Hashtable<String, Object> userFilters;
			
			FeatureSelection selection;
			
			// Build the 'statement' on the fly.
			while( keys.hasMoreElements( ) ) {
				key = keys.nextElement( );
				
				statement2 = "device_usage.";
				
				if ((key == DEVICE_ID) || (key == USER_ID))
					statement = statement + statement2 + key + " = " + featureSelectionFilters.get(key);
				else
					statement = statement + statement2 + key + " = '" + featureSelectionFilters.get(key) + "'";
				
				if (keys.hasMoreElements())
					statement = statement + " AND ";
			}
			
			sql = ("SELECT *" +
					" FROM device_usage" +
					" WHERE " + statement);
			
			conn = postgreSQL.postgreSQLConn();
			
			// This query selects all data from the device usage table.
			query = conn.prepareStatement(sql);
			
			ResultSet results = query.executeQuery();
			
			// If the results returned from the database was empty, return a null value 
			// and there is no reason to continue to execute the rest of the code.
			if (results.wasNull()) {
				return null;
			}
			else {
			while (results.next()) {
				filters = new Hashtable<String, Object>();
				userFilters = new Hashtable<String, Object>();
				
				userFilters.put(UserDAO.USER_ID, results.getInt(USER_ID));
				
				User[] newUser = UserDAO.select(userFilters);

				User aUser = new User();
	
				// We only need the one user.
				aUser.setUserId(newUser[0].getUserId());
				aUser.setPassword(newUser[0].getPassword());
				aUser.setFirstName(newUser[0].getFirstName());
				aUser.setLastName(newUser[0].getLastName());
				
				aUser.setUserId(results.getInt(USER_ID));
				filters.put(DEVICE_ID, results.getInt(DEVICE_ID));
				filters.put(USER_ID, results.getInt(USER_ID));
				TrackedFeature[] track = TrackedFeatureDAO.select(filters);
				
				selection = new FeatureSelection(aUser, results.getTimestamp(START_TIME));
				
				selection.setEndDate(results.getTimestamp(END_TIME));
				
				for (int i = 0; i < track.length; i++) {
					selection.addNewTrackedFeature(track[i]);
				}
				
				getSelection.add(selection);
			}
			}
			
			query.close();
			}
			
		} catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( );     
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        } finally {
        	if (conn != null) conn.close();
        }
		
		FeatureSelection[] getSelectionList = (FeatureSelection[]) getSelection.toArray(new FeatureSelection[getSelection.size()]);
		
		if (getSelectionList.length > 0)
	    	return getSelectionList;
	    else
	    	return null;
	}
	
	/** 
	 * Method's purpose is to update device usage related information based on the provided hash 
	 * table filters
	 * @param deviceID
	 * device identification number
	 * @param userID
	 * user identification number
	 * @param args
	 * hash table
	 * @return
	 * Boolean
	 * @throws Exception
	 */
	public static boolean update(long deviceID, long userID, Hashtable<String, Object> args) throws Exception {
		Connection conn = null;
		PreparedStatement query = null;
        boolean isTrue = false;
        
        try {
        	// If the filters contained in the hash table are empty, return a null value 
        	// and do not proceed to execute the rest of the code.
        	if ((args.isEmpty()) || (deviceID <= 0) || (userID <= 0))
        		return false;
        	else {
        	
        	Date start = null, end;
        	String sql = "";
        	String key;
        	key = null;
        	
        	conn = postgreSQL.postgreSQLConn();
        	
        	Enumeration<String> keys = args.keys( );
            
        	// Build the 'statement' on the fly.
            while (keys.hasMoreElements()) {
            	key = keys.nextElement();
            	
            	sql = ("UPDATE device_usage"
            			+ " SET " + key + " = ?"
                        + " WHERE device_id = " + deviceID
                        + " AND user_id = " + userID);

            	switch (key) {
            		case START_TIME:
            		{
            			start = (Date) args.get(START_TIME);
                    	query = conn.prepareStatement(sql);
                    	query.setObject(1, start);
                    	int update = query.executeUpdate();
                    	query.close();
                    	// We must make sure that data was updated in the device usage
                		// table.
                    	if (update > 0)
                    		isTrue = true;
                    	break;
            		}
            		case END_TIME:
            		{
            			end = (Date) args.get(END_TIME);
            			if (end.before((Date) args.get(START_TIME))) {
            				isTrue = false;
            				break;
            			} else {
            			query = conn.prepareStatement(sql);
                    	query.setObject(1, end);
                    	int update = query.executeUpdate();
                    	query.close();
            			
                    	if (update > 0)
                    		isTrue = true;
            			}
                    	break;
            		}
            		default:
            			isTrue = false;
            			return isTrue;
            	}
            }
        	}
        	
        } catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( );     
            isTrue = false;
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        	isTrue = false;
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        	isTrue = false;
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        	isTrue = false;
        } finally {
        	if (conn != null) conn.close();
        }
        return isTrue;
	}

	/** 
	 * Method's purpose is to delete a single device usage information
	 * @param deviceID
	 * device identification number
	 * @param userID
	 * user identification number
	 * @return
	 * boolean
	 * @throws Exception
	 */
	public static boolean delete(long deviceID, long userID) throws Exception {
		Connection conn = null;
		PreparedStatement query = null;
        boolean isTrue = false;
        
        try {
        	if ((deviceID <= 0) || (userID <= 0))
        		return false;
        	else {
        	conn = postgreSQL.postgreSQLConn();
            	
        	query = conn.prepareStatement("DELETE FROM device_usage"
        			+ " WHERE device_id = ? AND user_id = ?");
            query.setObject(1, deviceID);
            query.setObject(2, userID);
        	int deleteUsage = query.executeUpdate();
            query.close();
            
            // We must make sure that data was deleted from the device usage
    		// table and its related data.
            if (deleteUsage > 0) {
            	isTrue = true;
            }
        	}
        	
        } catch (ArrayIndexOutOfBoundsException e) {
        	System.err.println ( e.getMessage( ) );
            e.printStackTrace ( );   
            isTrue = false;
        } catch (NullPointerException e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace ( );
        	isTrue = false;
        } catch (SQLException sqlError) {
        	System.err.println ( sqlError.getMessage( ) );
        	sqlError.printStackTrace ( );
        	isTrue = false;
        } catch (Exception e) {
        	System.err.println ( e.getMessage( ) );
        	e.printStackTrace();
        	isTrue = false;
        } finally {
        	if (conn != null) conn.close();
        }
        return isTrue;
	}
} // End of public class FeatureSelectionDAO